package com.lifang.imgsoa.util;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import lombok.extern.log4j.Log4j;

@Log4j
public class JdbcUtil {
	
	private static final String MYSQL_DRIVER = "com.mysql.jdbc.Driver";
//	private static final String MYSQL_MILESTONE_URL = "jdbc:mysql://121.40.93.62:3306/milestone?useUnicode=true&characterEncoding=UTF-8";
	private static final String MYSQL_LIFANG_URL = "jdbc:mysql://121.40.93.62:3306/lifang?useUnicode=true&characterEncoding=UTF-8";
	private static final String USER_NAME = "lifangdev";
//	private static final String PASS_WORD = "5E12U1Jh2bCXg3rQ";
	private static final String PASS_WORD = "ls54k2dk_3kl_lei";
	
	private static Connection connLifang = null;
	static {
		try {
			Class.forName(MYSQL_DRIVER);
			connLifang = DriverManager.getConnection(MYSQL_LIFANG_URL, USER_NAME, PASS_WORD);
		} catch (Exception e) {
			log.error("连接数据库出现错误，信息：" + e.getMessage(), e);
			e.printStackTrace();
		}
	}
	
	/**
	 * 连接数据库
	 * 
	 * @param driver
	 * @param url
	 * @param user
	 * @param password
	 * @return
	 */
	public static Connection getConnection(String driver, String url, String user, String password) {
		try {
			Class.forName(driver);
			Connection conn = DriverManager.getConnection(url, user, password);
			return conn;
		} catch (Exception e) {
			log.error(user + " 登陆失败:" + e.getMessage());
			e.printStackTrace();
			return null;
		}
	}
	
	/**
	 * 查询数据
	 * @param name 供应商名称
	 * @return 供应商ID
	 */
	public static List<Map<String, Object>> search(String sql, String[] parameter) {
		List<Map<String, Object>> retData = new ArrayList<Map<String,Object>>() ;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			if (connLifang == null) {
				connLifang = getConnection(MYSQL_DRIVER, MYSQL_LIFANG_URL, USER_NAME, PASS_WORD);
			}
			ps = connLifang.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				Map<String, Object> map = new HashMap<String, Object>() ;
				for (String string : parameter) {
					map.put(string, rs.getString(string)) ;
				}
				retData.add(map) ;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				ps.close();
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return retData;
	}
	
	/**
	 * 
	 * 功能描述: 拼接sql语句
	 *
	 * <pre>
	 *     周智明: 2015年3月26日  新建
	 * </pre>
	 *
	 * @param page 页码
	 * @param pageSize 每页记录数
	 * @return
	 */
	public static String getSql(int page, int pageSize) {
		String sql = "SELECT B.RES_ID AS RES_ID, B.VALUE AS VALUE, S.PIC_URL AS PIC_URL FROM  SYS_PIC_URL S , ("
				+ " SELECT H.PID AS PID, H.RES_ID AS RES_ID, P.VALUE AS VALUE FROM HOUSE_RES_PIC_URL H, SYS_PROP_OPTION P "
				+ "WHERE H.RES_PIC_TYPE=P.PROP_CODE AND P.VALUE IN('111','112','113','114','115','119','1020') and h.is_cover=1  GROUP BY H.PID  " 
				+ ") AS B WHERE S.PID = B.PID AND S.PIC_URL like '%house_resource%' AND B.RES_ID!=0 ORDER BY B.RES_ID ASC LIMIT " + (page-1) * pageSize + "," + pageSize ;
//		String sql = "SELECT B.RES_ID AS RES_ID, B.VALUE AS VALUE, S.PIC_URL AS PIC_URL FROM  SYS_PIC_URL S , ("
//				+ " SELECT H.PID AS PID, H.RES_ID AS RES_ID, P.VALUE AS VALUE FROM HOUSE_RES_PIC_URL H, SYS_PROP_OPTION P "
//				+ "WHERE H.RES_PIC_TYPE=P.PROP_CODE AND P.VALUE IN('111','112','113','114','115','119','1020')  GROUP BY H.PID  " 
//				+ ") AS B WHERE S.PID = B.PID AND S.PIC_URL like '%house_resource%' AND B.RES_ID!=0 AND B.RES_ID<789417 AND B.RES_ID>=719238 ORDER BY B.RES_ID ASC LIMIT " + (page-1) * pageSize + "," + pageSize ;
//		String sql = "SELECT B.RES_ID AS RES_ID, B.VALUE AS VALUE, S.PIC_URL AS PIC_URL FROM  SYS_PIC_URL S , ("
//				+ " SELECT H.PID AS PID, H.RES_ID AS RES_ID, P.VALUE AS VALUE FROM HOUSE_RES_PIC_URL H, SYS_PROP_OPTION P "
//				+ "WHERE H.RES_PIC_TYPE=P.PROP_CODE AND P.VALUE IN('111','112','113','114','115','119','1020')  GROUP BY H.PID  " 
//				+ ") AS B WHERE S.PID = B.PID AND S.PIC_URL like '%house_resource%' AND B.RES_ID!=0 AND B.RES_ID>=794120 ORDER BY B.RES_ID ASC LIMIT " + (page-1) * pageSize + "," + pageSize ;
		//789417 这个id之后的数据图片是更改好的.  跑完所有程序之后需要再跑这个ID之前的数据
		
		return sql ;
	}
	
	/**
	 * 
	 * 功能描述: 拼接sql语句
	 *
	 * <pre>
	 *     周智明: 2015年3月26日  新建
	 * </pre>
	 *
	 * @param page 页码
	 * @param pageSize 每页记录数
	 * @return
	 */
	public static String getSqlXiaoQv(int page, int pageSize) {
		String sql = "SELECT HP.HOUSE_ID AS HOUSE_ID, HP.PIC_TYPE AS PIC_TYPE, PIC.PIC_URL AS PIC_URL "
				+ "FROM HOUSE_N_PIC_INFO HP, SYS_PIC_URL PIC "
				+ "WHERE HP.PID = PIC.PID AND HP.IS_DELETE = 0 AND PIC.IS_DELETE = 0 "
				+ "AND HP.PIC_TYPE IN ('shEffect_pic','shHouseType_pic','shOther_pic','shHouse_pic','other_pic') "
				+ "AND PIC.PIC_URL IS NOT NULL AND HP.HOUSE_ID>=46142 ORDER BY HP.HOUSE_ID ASC LIMIT " + (page-1) * pageSize + "," + pageSize ;
		
		return sql ;
	}
	
	/**
	 *  生成增加的sql语句
	 * <pre>
	 *     周智明: 2015年3月26日  新建
	 * </pre>
	 *
	 * @param filePath 将生成的sql保存到本地地址
	 * @param insterSQL 生成的sql语句
	 * @throws IOException
	 */
	public static void createSQL(String filePath, String insertSQL) throws IOException {
		File file = new File(filePath);
		//是否需要创建文件
		if (!file.exists()) {
			file.createNewFile();
		}
		FileOutputStream fos = new FileOutputStream(file, true);
		OutputStreamWriter osw = new OutputStreamWriter(fos, "UTF-8");
		BufferedWriter bw = new BufferedWriter(osw);
		
		bw.write(insertSQL);
		bw.newLine();
		
		//关闭流
		bw.flush();
		bw.close();
		fos.flush();
		fos.close();
		osw.close();
	}
	
}
